package com.itheima.service.impl;

import com.itheima.domain.entity.Requirement;
import com.itheima.mapper.RequirementMapper;
import com.itheima.service.RequirementService;
import com.itheima.utils.CategoryUtil;
import com.itheima.utils.SqlUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Service
public class RequirementServiceImpl implements RequirementService {
    @Autowired
    private RequirementMapper requirementMapper;

    @Override
    public List<Requirement> top50() {
        return requirementMapper.top50();
    }

    @Override
    public List<Requirement> top20(String category) throws SQLException {
        String tb = CategoryUtil.getCategory(category);
        List<Requirement> result = queryTop20(category, tb);
        return result;
    }

    private static List<Requirement> queryTop20(String category, String tb) throws SQLException {
        SqlUtil sqlUtil = new SqlUtil();
        String sql = "select * from " + tb + "_requirement_analysis where category='" + category + "' and requirement != '<null>' order by count desc limit 20 ";
        ResultSet resultSet = sqlUtil.executeQuery(sql);
        List<Requirement> requirements = new ArrayList<>();
        while (resultSet.next()) {
            Requirement build = Requirement.builder()
                    .category(resultSet.getString("category"))
                    .requirement(resultSet.getString("requirement"))
                    .count(resultSet.getInt("count"))
                    .build();
            requirements.add(build);
        }
        sqlUtil.close();
        return requirements;
    }

}
